﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;
using System.Data;

namespace xiaolanshu
{
    class SQLTools
    {
        public static SqlConnection conn = new SqlConnection(
            "Server=sqlserver2012.rdssgclf3jloovx.rds.su.baidubce.com;" +
            "Database=xiaolanshu;" +
            "User Id=lishu;Password=lishu^_^");

        public static Byte[] imageToByte(string image_path)
        {
            FileStream fs = new FileStream(image_path, FileMode.Open);
            byte[] bytes = new byte[fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();

            return bytes;
        }

        public static void insertIntoLipstickInfo(SqlConnection conn, string lnum, string lname, string price, string color, string wei, string qgp, string image_path)
        {
            string sql = "insert into lipstick_infomation values (@lnum, @lname, @price, @color, @wei, @qgp, @photo, @clicknum)";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add("@lnum", SqlDbType.Char).Value = lnum;
            cmd.Parameters.Add("@lname", SqlDbType.Char).Value = lname;
            cmd.Parameters.Add("@price", SqlDbType.Char).Value = price;
            cmd.Parameters.Add("@color", SqlDbType.Char).Value = color;
            cmd.Parameters.Add("@wei", SqlDbType.Char).Value = wei;
            cmd.Parameters.Add("@qgp", SqlDbType.Date).Value = qgp;
            cmd.Parameters.Add("@photo", SqlDbType.Image).Value = imageToByte(image_path);
            cmd.Parameters.Add("@clicknum", SqlDbType.Int).Value = 0;

            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
        }

        public static void insertInitialLipstickInfo()
        {
            insertIntoLipstickInfo(conn, "l001", "兰蔻菁纯唇膏#196", "300", "朱砂橘", "3.4", "2024-12-12", @"D:\xls_lip\196.jpg");
            insertIntoLipstickInfo(conn, "l002", "兰蔻菁纯唇膏#888", "285", "小野莓", "3.4", "2024-12-12", @"D:\xls_lip\888.jpg");
            insertIntoLipstickInfo(conn, "l003", "兰蔻菁纯唇膏#118", "300", "琥珀红棕", "3.4", "2024-12-12", @"D:\xls_lip\118.jpg");
            insertIntoLipstickInfo(conn, "l004", "兰蔻小蛮腰唇釉#288", "300", "橘咖小奶猫", "3.4", "2024-12-12", @"D:\xls_lip\288.jpg");
            insertIntoLipstickInfo(conn, "l005", "兰蔻哑光唇膏礼盒", "600", "#196 #295", "6.8", "2024-12-12", @"D:\xls_lip\2.jpg");
            insertIntoLipstickInfo(conn, "l006", "兰蔻小蛮腰唇釉三支装", "900", "#888、#288、#196", "10.2", "2024-12-12", @"D:\xls_lip\3.jpg");
        }

        public static void insertIntoPack(string pname, string pphoto)
        {
            string sqlstr = "select * from 包装 where 1=0";
            SqlConnection conn = SQLTools.conn;
            SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, conn);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

            DataTable data = new DataTable();
            adapter.Fill(data);

            DataRow row = data.NewRow();
            row["pname"] = pname;
            row["pphoto"] = imageToByte(pphoto);
            data.Rows.Add(row);

            builder.GetUpdateCommand();
            adapter.Update(data);
        }

        public static void insertInitialPack()
        {
            insertIntoPack("机械银", @"D:\xls_lip\机械银.jpeg");
            insertIntoPack("少女粉", @"D:\xls_lip\少女粉.jpg");
            insertIntoPack("哑光黑", @"D:\xls_lip\哑光黑.jpg");
            insertIntoPack("中国红", @"D:\xls_lip\中国红.jpg");
            insertIntoPack("璀璨黑", @"D:\xls_lip\璀璨黑.jpg");
        }
    }
}
